﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Data;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using System.Collections;
using Microsoft.Win32;
using ElectronicWMSA.Comm.Forms.Message;
using NPOI.XSSF.UserModel;

///
///使用该类 需要添加 NOPI.dll 的引用
///版本为 2.0.0.0 
namespace ElectronicWMSA.Comm.ClassFiles
{
    public static class ExcelUtil
    {
        #region  导出Datatable的数据到Excel
        /// <summary>
        /// 导出一个Datatable的数据到Excel文件
        /// </summary>
        /// <param name="dt">需要导出的DataTable</param>
        public static void ExportDataTableToExcel(DataTable dt)
        {
            try
            {
                HSSFWorkbook wk = new HSSFWorkbook();
                ISheet sheet = wk.CreateSheet("Sheet1");

                int RowCount = dt.Rows.Count;
                int ColumnCount = dt.Columns.Count;


                IRow row = sheet.CreateRow(0);
                for (int j = 0; j < ColumnCount; j++) //列标题
                {
                    sheet.SetColumnWidth(j, 20 * 256);
                    ICell cell = row.CreateCell(j);
                    cell.SetCellType(CellType.String);
                    cell.SetCellValue(dt.Columns[j].ColumnName);
                }

                for (int i = 0; i < RowCount; i++)
                {
                    row = sheet.CreateRow(i + 1);

                    for (int j = 0; j < ColumnCount; j++)
                    {
                        sheet.SetColumnWidth(j, 20 * 256);
                        ICell cell = row.CreateCell(j);
                        cell.SetCellType(CellType.String);
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }

                }

                SaveFileDialog dlg = new SaveFileDialog();
                dlg.Filter = "Execl files (*.xls)|*.xls";
                dlg.FilterIndex = 0;
                dlg.RestoreDirectory = true;
                dlg.CreatePrompt = true;
                dlg.Title = "保存为Excel文件";
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    using (FileStream fs = File.OpenWrite(dlg.FileName)) //打开一个xls文件，如果没有则自行创建，如果存在文件则在创建是不要打开该文件！
                    {
                        wk.Write(fs);   //向打开的这个xls文件中写入Sheet1表并保存。
                        MessageClass.right("导出成功！");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageClass.error(ex.Message);
            }
        }

        /// <summary>
        /// 导出一个DataGridView的数据到Excel文件
        /// </summary>
        /// <param name="Gridview">绑定了数据源的DataGridView控件</param>
        public static void ExportDataGridViewToExcel(DataGridView Gridview)
        {
            HSSFWorkbook wk = new HSSFWorkbook();
            ISheet sheet = wk.CreateSheet("Sheet1");

            int RowCount = Gridview.RowCount;
            int ColumnCount = Gridview.ColumnCount;


            IRow row = sheet.CreateRow(0);
            for (int j = 0; j < ColumnCount; j++) //列标题
            {
                sheet.SetColumnWidth(j, 20 * 256);
                ICell cell = row.CreateCell(j);
                cell.SetCellType(CellType.String);
                cell.SetCellValue(Gridview.Columns[j].HeaderText);
            }

            for (int i = 0; i < RowCount; i++)
            {
                row = sheet.CreateRow(i + 1);

                for (int j = 0; j < ColumnCount; j++)
                {
                    sheet.SetColumnWidth(j, 20 * 256);
                    ICell cell = row.CreateCell(j);
                    cell.SetCellType(CellType.String);
                    if (Gridview.Rows[i].Cells[j].Value != null)
                    {
                        cell.SetCellValue(Gridview.Rows[i].Cells[j].Value.ToString());
                    }
                }

            }

            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Execl files (*.xls)|*.xls";
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;
            dlg.CreatePrompt = true;
            dlg.Title = "保存为Excel文件";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                using (FileStream fs = File.OpenWrite(dlg.FileName)) //打开一个xls文件，如果没有则自行创建，如果存在文件则在创建是不要打开该文件！
                {
                    wk.Write(fs);   //向打开的这个xls文件中写入Sheet1表并保存。
                    MessageClass.right("导出成功！");
                }
            }
        }
        #endregion

        #region 读取Excel文件（只读取该文件的第一个Sheet的数据）
        /// <summary>
        /// 读取一个Excel文件（只读取该文件的第一个Sheet的数据）
        /// </summary>
        /// <returns>返回一个包含Excel文件数据的DataTable</returns>
        public static DataTable ReadExcelToDataTable()
        {
            DataTable dt = new DataTable();
            OpenFileDialog OpenDialog = new OpenFileDialog();
            OpenDialog.Filter = "Excel Files|*.xls|Excel Files|*.xlsx";
            OpenDialog.FilterIndex = 0;
            OpenDialog.RestoreDirectory = true;
            OpenDialog.Title = "读取Excel文件";

            OleDbConnection con;
            OleDbDataAdapter da;
            DataSet ds = new DataSet();

            if (OpenDialog.ShowDialog() == DialogResult.OK)
            {
                if (string.IsNullOrEmpty(OpenDialog.FileName))
                {
                    MessageClass.error("请选择Excel文件！");
                    return dt;
                }
                else
                {
                    try
                    {
                        string filename = OpenDialog.FileName.Substring(OpenDialog.FileName.LastIndexOf('.'));
                        string connStr = "";
                        switch (filename)
                        {
                            case ".xls":
                                {
                                    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                                    break;
                                }
                            case ".xlsx":
                                {
                                    connStr = "Provider=Microsoft.Ace.OLEDB.12.0;data source=" + OpenDialog.FileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                                    break;
                                }
                            default:
                                {
                                    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                                    break;

                                }
                        }

                        string sql = "SELECT  * FROM [" + GetExcelFirstTableName(OpenDialog.FileName, connStr)[0].ToString() + "]  ";

                        con = new OleDbConnection(connStr);
                        con.Open();
                        da = new OleDbDataAdapter(sql, con);
                        da.Fill(ds);
                        if (ds.Tables[0].Rows.Count == 0)
                        {
                            MessageClass.error("选择的文件没数据！");
                            return dt;
                        }
                        else
                        {
                            dt = ds.Tables[0];
                        }
                        return dt;
                    }
                    catch (Exception ex)
                    {
                        MessageClass.error(ex.Message);
                        return dt;
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 读取一个Excel文件（读取该文件的所有Sheet的数据，但是该文件的所有Sheet数据格式必须一致）
        /// </summary>
        /// <returns>返回一个包含Excel文件数据的DataTable</returns>
        public static DataTable ReadMoreExcelToDataTable()
        {
            DataTable dt = new DataTable();
            OpenFileDialog OpenDialog = new OpenFileDialog();
            OpenDialog.Filter = "Excel Files|*.xls|Excel Files|*.xlsx";
            OpenDialog.FilterIndex = 0;
            OpenDialog.RestoreDirectory = true;
            OpenDialog.Title = "读取Excel文件";

            OleDbConnection con;
            OleDbDataAdapter da;
            DataSet ds = new DataSet();

            if (OpenDialog.ShowDialog() == DialogResult.OK)
            {
                if (string.IsNullOrEmpty(OpenDialog.FileName))
                {
                    MessageClass.error("请选择Excel文件！");
                    return dt;
                }
                else
                {
                    try
                    {
                        string filename = OpenDialog.FileName.Substring(OpenDialog.FileName.LastIndexOf('.'));
                        string connStr = "";
                        switch (filename)
                        {
                            case ".xls":
                                {
                                    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                                    break;
                                }
                            case ".xlsx":
                                {
                                    connStr = "Provider=Microsoft.Ace.OLEDB.12.0;data source=" + OpenDialog.FileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                                    break;
                                }
                            default:
                                {
                                    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                                    break;

                                }
                        }
                        ArrayList sqllist = new ArrayList();
                        ArrayList tablelist = new ArrayList();
                        tablelist = GetExcelAllTableName(OpenDialog.FileName, connStr);
                        for (int i = 0; i < tablelist.Count; i++)
                        {
                            sqllist.Add("SELECT  * FROM [" + tablelist[i] + "]");
                        }
                        con = new OleDbConnection(connStr);
                        con.Open();
                        for (int i = 0; i < sqllist.Count; i++)
                        {
                            da = new OleDbDataAdapter(sqllist[i].ToString(), con);
                            da.Fill(ds);
                        }
                        if (ds.Tables[0].Rows.Count == 0)
                        {
                            MessageClass.error("选择的文件没数据！"); return dt;
                        }
                        else
                        {
                            dt = ds.Tables[0];
                        }
                        return dt;
                    }
                    catch (Exception ex)
                    {
                        MessageClass.error(ex.Message); return dt;
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 读取一个Excel文件（读取该文件的所有Sheet的数据，该文件的Sheet数据格式可以一致）
        /// </summary>
        /// <returns>返回一个包含Excel文件数据的DataSet</returns>
        public static DataSet ReadMoreNotSameExcelToDataTable()
        {
            DataTable dt = new DataTable();
            OpenFileDialog OpenDialog = new OpenFileDialog();
            OpenDialog.Filter = "Excel Files|*.xls|Excel Files|*.xlsx";
            OpenDialog.FilterIndex = 0;
            OpenDialog.RestoreDirectory = true;
            OpenDialog.Title = "读取Excel文件";

            OleDbConnection con;
            OleDbDataAdapter da;
            DataSet ds = new DataSet();

            if (OpenDialog.ShowDialog() == DialogResult.OK)
            {
                if (string.IsNullOrEmpty(OpenDialog.FileName))
                {
                    MessageClass.error("请选择Excel文件！");
                    return ds;
                }
                else
                {
                    try
                    {
                        string filename = OpenDialog.FileName.Substring(OpenDialog.FileName.LastIndexOf('.'));
                        string connStr = "";
                        switch (filename)
                        {
                            case ".xls":
                                {
                                    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                                    break;
                                }
                            case ".xlsx":
                                {
                                    connStr = "Provider=Microsoft.Ace.OLEDB.12.0;data source=" + OpenDialog.FileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                                    break;
                                }
                            default:
                                {
                                    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                                    break;

                                }
                        }
                        ArrayList sqllist = new ArrayList();
                        ArrayList tablelist = new ArrayList();
                        tablelist = GetExcelAllTableName(OpenDialog.FileName, connStr);
                        for (int i = 0; i < tablelist.Count; i++)
                        {
                            sqllist.Add("SELECT  * FROM [" + tablelist[i] + "]");
                        }
                        con = new OleDbConnection(connStr);
                        con.Open();
                        for (int i = 0; i < sqllist.Count; i++)
                        {
                            da = new OleDbDataAdapter(sqllist[i].ToString(), con);
                            da.Fill(ds, "Table" + i);
                        }
                        bool isHave = false;
                        for (int i = 0; i < ds.Tables.Count; i++)
                        {
                            if (ds.Tables[i].Rows.Count != 0)
                            {
                                isHave = true;
                            }
                        }
                        if (!isHave)
                        {
                            MessageClass.error("选择的文件没数据！"); return ds;
                        }
                        return ds;
                    }
                    catch (Exception ex)
                    {
                        MessageClass.error(ex.Message); return ds;
                    }
                }
            }
            return ds;
        }

        public static NPOI.XSSF.UserModel.XSSFWorkbook DataTableExport(DataTable dt)
        {
            NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
            try
            {
                if (dt.Rows.Count > 0)
                {
                    workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
                    ISheet sheet = workbook.CreateSheet("Sheet1");
                    ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                    HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                    //IFont headerfont = workbook.CreateFont();
                    //headerfont.Boldweight = (short)FontBoldWeight.Bold;
                    //HeadercellStyle.SetFont(headerfont);

                    ICellStyle cellStyle1 = workbook.CreateCellStyle();
                    cellStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
                    cellStyle1.VerticalAlignment = VerticalAlignment.Center;
                    cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                    int RowCount = dt.Rows.Count;
                    int ColumnCount = dt.Columns.Count;

                    IRow row = sheet.CreateRow(0);
                    for (int j = 0; j < ColumnCount; j++)
                    {
                        ICell cell = row.CreateCell(j);
                        cell.SetCellType(CellType.String);
                        cell.SetCellValue(dt.Columns[j].ColumnName);
                    }
                    for (int i = 0; i < RowCount; i++)
                    {
                        row = sheet.CreateRow(i + 1);
                        for (int j = 0; j < ColumnCount; j++)
                        {
                            ICell cell = row.CreateCell(j);
                            var values = dt.Rows[i][j].ToString().Trim();
                            if (values != "")
                            {
                                if (ValidationRegex.ValidatePlusMinus(values) ||
                                    ValidationRegex.ValidteDataFloat(values))
                                {
                                    cell.SetCellType(CellType.Numeric);
                                    cell.SetCellValue(Convert.ToDouble(values));
                                }
                                else
                                {
                                    cell.SetCellType(CellType.String);
                                    cell.SetCellValue(values);
                                }
                            }
                            else
                            {
                                cell.SetCellType(CellType.String);
                                cell.SetCellValue("");
                            }
                            cell.CellStyle = cellStyle1;
                        }
                    }
                    for (int i = 0; i < ColumnCount; i++) { sheet.AutoSizeColumn(i); }
                }
                else
                {
                    MessageClass.error("无数据！");
                }
            }
            catch (Exception)
            {
            }
            return workbook;
        }

        /// <summary>
        /// 获取第一个Sheet的名称
        /// </summary>
        /// <param name="excelFileName">Excel文件名称</param>
        /// <param name="strExtension">读取Excel文件数据的数据连接</param>
        /// <returns></returns>
        private static ArrayList GetExcelFirstTableName(string excelFileName, string strExtension)
        {

            ArrayList tablenamelist = new ArrayList();
            try
            {
                if (File.Exists(excelFileName))
                {
                    using (OleDbConnection conn = new OleDbConnection(strExtension))
                    {
                        conn.Open();
                        DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        tablenamelist.Add(dt.Rows[0][2].ToString().Trim());
                        return tablenamelist;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageClass.error(ex.Message.ToString());
            }
            return tablenamelist;

        }

        /// <summary>
        /// 获取所有Sheet的名称
        /// </summary>
        /// <param name="excelFileName">Excel文件名称</param>
        /// <param name="strExtension">读取Excel文件数据的数据连接</param>
        /// <returns></returns>
        private static ArrayList GetExcelAllTableName(string excelFileName, string strExtension)
        {

            ArrayList tablenamelist = new ArrayList();
            try
            {
                if (File.Exists(excelFileName))
                {
                    using (OleDbConnection conn = new OleDbConnection(strExtension))
                    {
                        conn.Open();
                        DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            tablenamelist.Add(dt.Rows[i][2].ToString().Trim());
                        }
                        return tablenamelist;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageClass.error(ex.Message.ToString());
            }
            return tablenamelist;

        }
        #endregion

        /// <summary>
        /// 检查本机 Office
        /// </summary>
        /// <returns></returns>
        public static int OfficeExistsRegedit()
        {
            int ifused = 0;
            RegistryKey rk = Registry.LocalMachine;

            //查询Office2003
            RegistryKey f03 = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\11.0\Excel\InstallRoot\");

            //查询Office2007
            RegistryKey f07 = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\12.0\Excel\InstallRoot\");

            ////查询wps
            //RegistryKey wps = rk.OpenSubKey(@"SOFTWARE\Kingsoft\Office\6.0\common\");

            //检查本机是否安装Office2003
            if (f03 != null)
                ifused = 1;

            return ifused;
        }
    }
}
